// 建立路由模块
// 添加 express 依赖
const express = require("express")
// 添加路由依赖
const router = express.Router()
// 引入数据库文件
const {db,genid} = require("../db/DbUtils")


// 查找单篇博客文章接口
router.get("/detail", async (req,res)=>{

     let {id} = req.query;
     let detail_sql = "SELECT * FROM `blog` WHERE `id` = ?"
     let {err,rows} = await db.async.all(detail_sql,[id])
 
     if(err == null){
         
         res.send({
             code:200,
             msg:"获取成功",
             rows

         })
     }
     else{
         res.send({
             code:500,
             msg: "获取失败"
         })
     }

})


//查询博客 查询功能
router.get("/search", async (req,res)=>{
 
    /**
     *  1、首先需要 关键字 keyword 同时作用在 title 和 content 里
     *     再者还需要 关键字 categoryId 分类
     *     两个关键字都是有则执行，没有就不执行
     * 
     *  2、分页
     *     page 页码
     *     pageSize 分页大小
     * 
     * 
     */

    // 获取所有变量值,通过 query 来获取
    let {keyword,categoryId,page,pageSize} = req.query
     
    // 判断相关变量是否为空
    page = page == null? 1 : page;
    pageSize = pageSize == null? 10 : pageSize
    categoryId = categoryId == null? 0 : categoryId
    keyword = keyword == null? "" : keyword

    // 条件变量数组
    let params = []
    // sql 条件语句 数据
    let whereSqls = []

    

    if(categoryId != 0){
        whereSqls.push(" `category_id` = ? ")
        params.push(categoryId)
    }

    if(keyword != ""){
        whereSqls.push(" (`title` LIKE ? OR `content` LIKE ?) ")
        // 添加标题和文本内容模糊关键字判断
        params.push( "%" + keyword + "%")
        params.push( "%" + keyword + "%")
    }

    let whereSqlStr = ""
    if(whereSqls.length > 0){
        whereSqlStr = " WHERE " + whereSqls.join(" AND ")
    }

    //              博客分页查抄    按照建立事件排序 同时 进行分页排序 当前展示分页序列，从当前序列向后多少条数据
    let searchSql = "SELECT `id`,`category_id`, `create_time`, `title`, substr(`content`,0,50) AS `content` FROM `blog` " + whereSqlStr + " ORDER BY `create_time` DESC LIMIT ?,? "

                          // 根据 params 数组向后添加 当前页面号和页面最大数据展示号
    let searchSqlParams = params.concat([(page - 1) * pageSize, pageSize])

    // 查找符合所有模糊数据查找的所有数据总数
    // let searchCountSql = "SELECT  count(*) FROM 'blog' " + whereSqlStr;
    let searchCountSql = " SELECT count(*) AS `count` FROM `blog` " + whereSqlStr;
    // 查询符合相关条件的所有数据总数的变量
    let searchCountParams = params



    // 想数据库发送分页数据语句命令
    let searchResult = await db.async.all(searchSql,searchSqlParams)
    let countResult = await db.async.all(searchCountSql,searchCountParams)

    console.log(searchSql,countResult)

    // 只有分页数据不报错，分页总数不报错
    if(searchResult.err == null && countResult.err == null)
    {
         res.send({
             code:200,
             msg:"查询成功",
             data:{
                 keyword,
                 categoryId,
                 page,
                 pageSize,
                 rows: searchResult.rows,
                //  count:countResult.rows[0]["count(*)"]   // 这种样式不好看
                count:countResult.rows[0].count
             }
         })
    }
    else{
        res.send({
            code:500,
            msg:"查询失败"
        })
    }

});

//删除博客
router.delete("/_token/delete", async (req,res) => {
   
    //  id
    let  id  = req.query.id

    const delete_sql = "DELETE FROM `blog` WHERE `id` = ?"

    let {err,rows} = await db.async.run(delete_sql,[id])

    if(err == null){
        
        res.send({
            code:200,
            msg:"删除成功"
        })
    }
    else{
        res.send({
            code:500,
            msg:"删除失败"
        })
    }
})

//修改博客
router.put("/_token/update", async (req,res) => {
   
    //  id
    let { id,title,categoryId,content } = req.body;
    let create_time = new Date().getTime();

    const update_sql = "UPDATE `blog` SET `title` = ?,`content` = ?,`category_id` = ? WHERE `id` = ?"

    let params = [title,content, categoryId,id]

    let {err,rows} = await db.async.run(update_sql,params)

    console.log(err,rows);

    if(err == null){
        
        res.send({
            code:200,
            msg:"修改成功"
        })
    }
    else{
        res.send({
            code:500,
            msg:"修改失败"
        })
    }
})



 

//添加博客
router.post("/_token/add", async (req,res) => {
   
    //  标题，博客id,内容
    let { title,categoryId,content} = req.body;
    // 生成id 
    let id = genid.NextId()
    // 获取当前建立时间
    let create_time = new Date().getTime();

    const insert_sql = "INSERT INTO `blog`(`id`,`title`,`category_id`,`content`,`create_time`) VALUES (?,?,?,?,?)"
    let params = [id,title,categoryId,content,create_time]

    let {err,rows} = await db.async.run(insert_sql,params)

    console.log(err +" : " + rows);

    if(err == null){
        
        res.send({
            code:200,
            msg:"添加成功"
        })
    }
    else{
        res.send({
            code:500,
            msg:"添加失败"
        })
    }


})

// 将路由对象导出
module.exports = router